package persistence;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import domain.Class;
import domain.PTime;
import exception.IllegalHourException;
import exception.IllegalMinuteException;

public class ClassHandler {
	
	private final String SEPARATOR = ", ";
	private final String EMPTY_STRING = "";

	Statement stmt;
	
	

	public ClassHandler() throws SQLException {
		Connection conn;
		try {
			conn = DBConnection.getInstance().getDb_conn();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public boolean create(Class c) throws SQLException {

		String sqlComand = "INSERT INTO Classes VALUES (";
		String sqlComand2 = "INSERT INTO FreeSchedules VALUES (";

		try {

			sqlComand += c.getName()
					+ SEPARATOR
					+ c.getDiscipline().getCode()
					+ SEPARATOR
					+ c.getAccountable().getRegistration()
					+ ");";	
			
			stmt.execute(sqlComand);
			
			for (PTime ptime : c.getFreeSchedules()) {
				String sqlComandPartial = sqlComand2 + c.getName() + SEPARATOR
						+ c.getDiscipline().getCode() + SEPARATOR
						+ ptime.getHour() 
						+ ptime.getMinute() 
						+ ");";
				stmt.execute(sqlComandPartial);
			}
				

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	public boolean delete(String name, String disciplineCode,
			String professorReg) {

		String sqlComand = "DELETE FROM Classes WHERE ";

		try {
			sqlComand += "name = " + name + SEPARATOR 
					+ "disciplineCode = " + disciplineCode + SEPARATOR
					+ "professorReg = " + professorReg + ";";
			
			stmt.execute(sqlComand);

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	public boolean update(String name, String disciplineCode,
			String professorReg, List<PTime> freeSchedules) {

		String sqlComand = "UPDATE Classes SET ";
		String sqlComand2 = "UPDATE FreeSchedules SET ";

		try {
			if (professorReg != EMPTY_STRING) sqlComand += "professorReg = " + professorReg ;
			if (freeSchedules != null) {
				for (PTime pTime : freeSchedules) {
					sqlComand2 += "hour = " + pTime.getHour() + "minute = " + pTime.getMinute()
							+ "WHERE name = " + name + " AND "
							+ "disciplineCode = " + disciplineCode + ";";
					stmt.execute(sqlComand2);
				}
			}
			
			sqlComand += "WHERE name = " + name + " AND " + "disciplineCode = " + disciplineCode + ";";
			
			stmt.execute(sqlComand);

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	public ArrayList<Class> retreive(String disciplineCode) throws IllegalHourException, IllegalMinuteException {

		ResultSet stmtResult, stmtResult2;
		ArrayList<Class> result = new ArrayList<Class>();
		List<PTime> ptimes = new ArrayList<PTime>();

		String sqlComand = "SELECT * FROM Classes WHERE ";
		String sqlComand2 = "SELECT ptime FROM FreeSchedules WHERE ";

		try {
			
			DisciplineHandler discH = new DisciplineHandler();
			ProfessorHandler profH = new ProfessorHandler();
			
			sqlComand += "disciplineCode = " + disciplineCode + ";";
			stmtResult = stmt.executeQuery(sqlComand);
			
			while(stmtResult.next()){
				sqlComand2 += "name = " + stmtResult.getString(1) + ";";
				stmtResult2 = stmt.executeQuery(sqlComand2);
				while (stmtResult2.next()){
					ptimes.add(new PTime(stmtResult2.getInt(1), stmtResult2.getInt(2)));
				}
				Class classC = new Class(discH.retreive(disciplineCode).get(0), profH.retreive(stmtResult.getString(3)) , stmtResult.getString(1));
				classC.setFreeSchedules(ptimes);				
				result.add(classC);
			}
			
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
